package Dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.sqlite.SQLiteDataSource;

import Rule.chartoDBC;
;

public class DaoSqlData {

	/**
	 * 获取数据库中prdt表的第一列所有名称
	 * @param sqlStr sql select语句
	 * @return 名称的List集合
	 * @throws Exception
	 */
	public static List<String> getPrdName() throws Exception   {
		
		String sqlStr = "select prd_Name from prdt"; 
		List<String> sqlDataList = new ArrayList<String>();		
		Connection con = Dao.ConnectSqlite();
		try{			
			Statement sql = con.createStatement();
			ResultSet rs = sql.executeQuery(sqlStr);
			while(rs.next()){
				sqlDataList.add(rs.getString(1));
			}				
		}catch(Exception e){
			System.out.println("查询品名失败："+e.getMessage());
		}finally{
			try{
				if(con != null){
					con.close();
					con=null;
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		}		
		return sqlDataList;		
	}
	
	/**
	 * 自定义查询所有信息(顺序)
	 * @param sqlFieldName 要查询表的字段名称
	 * @param FieldData 要查询的数据
	 * @return 信息的ResultSet集合
	 * @throws Exception
	 */
	public static ResultSet getPrdtInfoByAsc(String sqlFieldName,String FieldData)  {
			
		Connection con = null;
		Statement sql = null;
		ResultSet rs = null;
		String sqlStr = "select * from prdt where "+ sqlFieldName + " like '%" + FieldData + "%'";
		try {
			con = Dao.ConnectSqlite();
			sql = con.createStatement();
			rs = sql.executeQuery(sqlStr);		
		} catch (Exception e) {
			e.printStackTrace();
		}		
		return rs;	
	}
	
	/**
	 * 自定义查询所有信息(倒序)
	 * @param sqlFieldName 要查询表的字段名称
	 * @param FieldData 要查询的数据
	 * @return 信息的ResultSet集合
	 * @throws Exception
	 */
	public static ResultSet getPrdInfoByDesc()  {
			
		Connection con = null;
		Statement sql = null ;
		ResultSet rs = null;
		String sqlStr = "select * from prdt  order by id desc";
		try {
			con = Dao.ConnectSqlite();
			sql = con.createStatement();
			rs = sql.executeQuery(sqlStr);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rs;		
	}
		
	/**
	 * 向数据库插入产品信息（单个）
	 * @param sName 要插入的品名
	 * @param sNo 要插入的品号
	 * @param fPri 要插入的金额
	 * @return boolean值
	 * @throws Exception
	 */
	public static boolean setPrdInfo_singel(String sName, int iNo, float fPri)  {
		
		boolean b = false;
		int iUpdateSuccessCount ;
		//String s = "insert into prdt(prd_Name,prd_No,pri) values ('双面贴','801010002','0.001')";	
		String sqlStr = "insert into prdt(prd_Name,prd_No,pri) values ('" +
				sName + "','" + iNo + "','" + fPri +"')";
		Connection 	con = null;
		Statement sql = null;
		try{
			con = Dao.ConnectSqlite();
			sql = con.createStatement();
			iUpdateSuccessCount = sql.executeUpdate(sqlStr);
			if(iUpdateSuccessCount != 0){
				b = true;
			}
		}catch(Exception e){
			System.out.println("插入数据失败："+e.getMessage());
		}finally{
			try{
				if(con != null){
					con.close();
					con = null;
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		return b;	
	}
	
	public static int setPrdInfo_multi(ArrayList<String> tableValues){
		
		int[] iUpdateSuccessCount = null ;
		String sName = null;
		int iNo = 0;
		float fPri = 0;		
		int forCount = tableValues.size() / 3;
		String sqlStr = "insert into prdt(prd_name,prd_No,pri) values (?,?,?);";
		Connection con = null;
		PreparedStatement sql = null;
		try{
			con = Dao.ConnectSqlite();
			sql = con.prepareStatement(sqlStr);
			con.setAutoCommit(false);
			for(int x = 0; x<forCount; x++){			
				sName = tableValues.get(3*x).trim().toString();
				iNo = Integer.valueOf(tableValues.get(3*x+1).trim().toString());
				fPri = Float.parseFloat(tableValues.get(3*x+2).trim().toString());	
				sql.setString(1, sName);
				sql.setInt(2, iNo);
				sql.setFloat(3, fPri);
				sql.addBatch();			
			}
			iUpdateSuccessCount=sql.executeBatch();
			con.commit();
		}catch(Exception e){
			System.out.println("插入数据失败："+e.getMessage());
		}finally{
			try{
				if(con != null){
					con.close();
					con = null;
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		}	
		return iUpdateSuccessCount.length;
	}
	
	/**
	 * 修改数据库中的产品信息
	 * @param sName 要修改的品名
	 * @param iNo 要修改的品号
	 * @param fPri 要修改的金额
	 * @param iNum 数据库中对应的ID号
	 * @return true or false
	 * @throws Exception
	 */
	public static boolean alterPrdInfo(String sName, int iNo, float fPri, int iNum) throws Exception{
		
		boolean b = false;
		int iAlterCount ;
		String sqlStr = "update prdt set prd_Name = '" + sName +"', prd_No = '"+ iNo 
						+"',  Pri = '" + fPri +"' where ID = '" + iNum +"'";
		Connection con = Dao.ConnectSqlite();
		try{
			Statement sql = con.createStatement();
			iAlterCount = sql.executeUpdate(sqlStr);
			if( iAlterCount !=0 ){
				b = true;
			}
		}catch(Exception e){
			System.out.println("更新数据失败："+ e.getMessage());
		}finally{
			try{
				if(con != null){
					con.close();
					con = null;
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		
		return b;
	}
}
